﻿Imports DTO
Imports System.Data.OleDb

Public Class PhieuThuDAO

    Public Function ThemPhieuThu(ByVal pt As PhieuThuDTO) As Boolean
        Dim kq As Boolean = True
        Dim cn As OleDbConnection
        'B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        cn = DatabaseProvider.ConnectionData()
        'B3: Tao chuoi strSQL thao tac CSDL
        Dim sql As String = "insert into PhieuThu(NgayThu,SoTienThu,idKhachHang,DaXoa) values(?,?,?,?)"
        Dim cmd As New OleDbCommand(sql, cn)
        cmd.Parameters.Add("@NgayThu", OleDbType.Date)
        cmd.Parameters.Add("@SoTienThu", OleDbType.Integer)
        cmd.Parameters.Add("@idKhachHang", OleDbType.Integer)
        cmd.Parameters.Add("@DaXoa", OleDbType.WChar)
        cmd.Parameters("@NgayThu").Value = pt.layNgayThu
        cmd.Parameters("@SoTienThu").Value = pt.LayTienThu
        cmd.Parameters("@idKhachHang").Value = pt.LayIDKhachHang
        cmd.Parameters("@DaXoa").Value = 0
        cmd.ExecuteNonQuery()
        cn.Close()
        Return kq

    End Function

    'Public Function LayPhieuThu(ByVal pt As PhieuThuDTO) As DataTable
    '    Dim kq As New DataTable
    '    Dim cn As OleDbConnection
    '    'B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
    '    cn = DatabaseProvider.ConnectionData()
    '    'B3: Tao chuoi strSQL thao tac CSDL
    '    Dim sql As String = "select * from PhieuThu where idKhachHang = ? and DaXoa = 0  "
    '    Dim cmd As New OleDbCommand(sql, cn)
    '    cmd.Parameters.Add("@idKhachHang", OleDbType.Integer)
    '    cmd.Parameters("@idKhachHang").Value = pt.LayIDKhachHang
    '    Dim a As New OleDb.OleDbDataAdapter(sql, cn)
    '    a.SelectCommand = cmd
    '    a.Fill(kq)
    '    cn.Close()
    '    Return kq
    'End Function
    Public Function LayPhieuThu(ByVal pt As PhieuThuDTO) As DataTable
        Dim kq As New DataTable
        Dim cn As OleDbConnection
        'B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        cn = DatabaseProvider.ConnectionData()
        'B3: Tao chuoi strSQL thao tac CSDL
        Dim sql As String = "select idPhieuThu,PhieuThu.idKhachHang, KhachHang.TenKhachHang,SoTienThu,NgayThu from PhieuThu, KhachHang where PhieuThu.idKhachHang = ? and PhieuThu.DaXoa = 0  and PhieuThu.idKhachHang =  KhachHang.idKhachHang "
        Dim cmd As New OleDbCommand(sql, cn)
        cmd.Parameters.Add("@PhieuThu.idKhachHang", OleDbType.Integer)
        cmd.Parameters("@PhieuThu.idKhachHang").Value = pt.LayIDKhachHang
        Dim a As New OleDb.OleDbDataAdapter(sql, cn)
        a.SelectCommand = cmd
        a.Fill(kq)
        cn.Close()
        Return kq
    End Function
    Public Function loadPhieuThu() As DataTable
        Dim kq As New DataTable
        Dim cn As OleDbConnection
        'B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        cn = DatabaseProvider.ConnectionData()
        'B3: Tao chuoi strSQL thao tac CSDL
        Dim sql As String = "select idPhieuThu,PhieuThu.idKhachHang, KhachHang.TenKhachHang,SoTienThu,NgayThu from PhieuThu, KhachHang where  PhieuThu.DaXoa = 0  and PhieuThu.idKhachHang =  KhachHang.idKhachHang  "

        Dim a As New OleDb.OleDbDataAdapter(sql, cn)
        a.Fill(kq)
        cn.Close()
        Return kq
    End Function

    Public Function BaoCaoCongNo(ByVal pt As PhieuThuDTO, ByVal kh As KhachHangDTO, ByRef thang As Integer) As DataTable
        Dim kq As New DataTable
        Dim cn As OleDbConnection
        'B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        cn = DatabaseProvider.ConnectionData()
        'B3: Tao chuoi strSQL thao tac CSDL
        Dim sql As String
        sql = "select kh.TenKhachHang, pt.SoTienThu , kh.TongTienNo from KhachHang kh , PhieuThu pt where  kh.idKhachHang = pt.idKhachHang and Month(pt.NgayThu) = ? "
        'B4: Thuc thi chuoi strSQL
        Dim cmd As New OleDbCommand(sql, cn)
        cmd.Parameters.Add("@thang", OleDbType.Integer)
        cmd.Parameters("@thang").Value = thang
        Dim a As New OleDb.OleDbDataAdapter()
        a.SelectCommand = cmd
        a.Fill(kq)
        cn.Close()
        Return kq
    End Function

    Public Sub XoaPhieuThu(ByVal pt As PhieuThuDTO)

        Dim cn As OleDbConnection
        'B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        cn = DatabaseProvider.ConnectionData()
        'B3: Tao chuoi strSQL thao tac CSDL
        Dim sql As String = "update PhieuThu set DaXoa = 1 where idPhieuThu = ? "
        Dim cmd As New OleDbCommand(sql, cn)
        cmd.Parameters.Add("@idpt", OleDbType.Integer)
        cmd.Parameters("@idpt").Value = pt.LayIdPhieuThu
        cmd.ExecuteNonQuery()
        cn.Close()
    End Sub

    Public Function UpdatePhieuThu(ByVal pt As PhieuThuDTO) As Boolean
        Dim kq As Boolean = True
        Dim cn As OleDbConnection
        'B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        cn = DatabaseProvider.ConnectionData()
        'B3: Tao chuoi strSQL thao tac CSDL
        Dim sql As String = "update PhieuThu set NgayThu = ? , SoTienThu = ? ,idKhachHang = ? where idPhieuThu= ?"
        Dim cmd As New OleDbCommand(sql, cn)

        cmd.Parameters.Add("@ngaythu", OleDbType.Date)
        cmd.Parameters.Add("@tienthu", OleDbType.Integer)
        cmd.Parameters.Add("@idkh", OleDbType.Integer)
        cmd.Parameters.Add("@idhd", OleDbType.Integer)

        cmd.Parameters("@ngaythu").Value = pt.layNgayThu
        cmd.Parameters("@tienthu").Value = pt.LayTienThu
        cmd.Parameters("@idkh").Value = pt.LayIDKhachHang
        cmd.Parameters("@idhd").Value = pt.LayIdPhieuThu

        cn.Close()
        Return kq
    End Function

    Public Function RefreshPhieuThu(ByVal pt As PhieuThuDTO) As DataTable
        Dim kq As New DataTable
        Dim cn As OleDbConnection
        'B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        cn = DatabaseProvider.ConnectionData()
        'B3: Tao chuoi strSQL thao tac CSDL
        Dim sql As String = "select * from PhieuThu where idKhachHang = ? and DaXoa = 0  "
        Dim cmd As New OleDbCommand(sql, cn)
        cmd.Parameters.Add("@idKhachHang", OleDbType.Integer)
        cmd.Parameters("@idKhachHang").Value = pt.LayIDKhachHang
        Dim a As New OleDb.OleDbDataAdapter(sql, cn)
        a.SelectCommand = cmd
        a.Fill(kq)
        cn.Close()
        Return kq
    End Function

End Class
